package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import oop.data.Mix;
import oop.data.MixData;
import oop.data.Test;
import oop.db.Database;

import com.google.gson.JsonParseException;

public class MixDAO {

	public static Random random = new Random(System.currentTimeMillis());

	public static List<Mix> fetchById(long id)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT mix_id, mix_code, mix_test, mix_data "
					+ "FROM tblMix "
					+ "WHERE mix_id = ?";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, id);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<Mix> fetchByTest(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT mix_id, mix_code, mix_test, mix_data"
					+ "FROM tblMix " 
					+ "WHERE mix_test = ?";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	protected static List<Mix> fromResultSet(ResultSet rs)
			throws JsonParseException, SQLException {
		List<Mix> mixes = new ArrayList<Mix>();
		while (rs.next()) {
			Test test = Test.fetchById(rs.getInt(3)).get(0);
			mixes.add(new Mix(rs.getLong(1), rs.getString(2), test, MixData
					.fromJson(rs.getString(4))));
		}
		return mixes;
	}

	public static Mix create(String code, Test test) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO `tblMix` (mix_test, mix_code, mix_data) "
					+ "VALUES (?, ?, ?)";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, test.getId());
			stmt.setString(2, code);
			MixData data = MixData.fromTest(test);
			stmt.setString(3, data.toJson());
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			rs.next();
			long id = rs.getLong(1);
			
			return new Mix(id, code, test, data);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
			if (rs != null) rs.close();
		}
	}

}
